package com.gaotu.demo.jdbc;

import com.gaotu.demo.User;
import com.gaotu.demo.UserDao;
import com.gaotu.demo.UserStatus;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Slf4j
@Component
public class StatementBasedUserDao implements UserDao {
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String userName;
    @Value("${spring.datasource.password}")
    private String password;

    @Autowired
    private DataSource dataSource;


    @Override
    public void insert(User user) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("insert into tb_user(`create_time`,`update_time`,`status`,`name`, `password`)")
                .append(" values (")
                .append("'").append(formatDate(user.getCreateTime())).append("',")
                .append("'").append(formatDate(user.getUpdateTime())).append("',")
                .append(user.getStatus().getCode()).append(",")
                .append("'").append(user.getName()).append("',")
                .append("'").append(user.getPassword()).append("'")
                .append(")");
        try (Connection connection = getConnectionFromDriverManger()){
            try (Statement statement = connection.createStatement()){
                int row = statement.executeUpdate(sqlBuilder.toString(),
                        Statement.RETURN_GENERATED_KEYS);
                if (row > 0){
                    try(ResultSet generatedKeys = statement.getGeneratedKeys()) {
                        if (generatedKeys.next()) {
                            long id = generatedKeys.getLong(1);
                            user.setId(id);
                        }
                    }
                }
            }catch (Exception e){
                log.error("failed to  insert user {}", user, e);
            }
        } catch (SQLException e) {
            log.error("failed to  insert user {}", user, e);
        }
    }

    @Override
    public void update(User user) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("update tb_user ")
                    .append(" set ")
                    .append("update_time = ").append("'").append(formatDate(user.getUpdateTime())).append("',")
                    .append("status = ").append(user.getStatus().getCode()).append(",")
                    .append("name = ").append("'").append(user.getName()).append("',")
                    .append("password = ").append("'").append(user.getPassword()).append("' ")
                    .append(" where ")
                    .append("id = ").append(user.getId());

        try (Connection connection = getConnectionFromDriverManger()){
            try (Statement statement = connection.createStatement()){
                statement.executeUpdate(sqlBuilder.toString());
            }catch (Exception e){
                log.error("failed to update user {}", user, e);
            }
        } catch (SQLException e) {
            log.error("failed to  update user {}", user, e);
            e.printStackTrace();
        }
    }

    @Override
    public void deleteById(Long id) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("delete from tb_user")
                .append(" where ").append("id = ").append(id);

        try (Connection connection = getConnectionFromDriverManger()){
//            connection.setAutoCommit(false);
            try (Statement statement = connection.createStatement()){
                statement.executeUpdate(sqlBuilder.toString());
//                connection.commit();
            }catch (Exception e){
                log.error("failed to delete user by id {}", id, e);
//                connection.rollback();
            }
        } catch (SQLException e) {
            log.error("failed to delete user by id {}", id, e);
        }
    }

    @Override
    public User getById(Long id) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("select id, create_time, update_time, status, name, password from tb_user ")
                .append(" where ").append("id = ").append(id);

        try (Connection connection = getConnectionFromDriverManger()){
            try (Statement statement = connection.createStatement()){
                try(ResultSet resultSet = statement.executeQuery(sqlBuilder.toString())) {
                    if (resultSet.next()) {
                        Long idForSelect = resultSet.getLong(1);
                        java.sql.Date createDate = resultSet.getDate(2);
                        java.sql.Date updateDate = resultSet.getDate(3);
                        Integer statusCode = resultSet.getInt(4);
                        String name = resultSet.getString(5);
                        String password = resultSet.getString(6);
                        User user = new User();
                        user.setId(idForSelect);
                        user.setCreateTime(createDate);
                        user.setUpdateTime(updateDate);
                        user.setStatus(UserStatus.valueOfCode(statusCode));
                        user.setName(name);
                        user.setPassword(password);
                        return user;
                    }
                }
            }catch (Exception e){
                log.error("failed to select user by {}", id, e);
            }
        } catch (SQLException e) {
            log.error("failed to select user by {}", id, e);
        }
        return null;
    }

    @Override
    public User getByName(String name) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("select id, create_time, update_time, status, name, password from tb_user ")
                .append(" where ").append("name = ").append(name);

        try (Connection connection = getConnectionFromDriverManger()){
            try (Statement statement = connection.createStatement()){
                try (ResultSet resultSet = statement.executeQuery(sqlBuilder.toString())) {
                    if (resultSet.next()) {
                        Long id = resultSet.getLong(1);
                        java.sql.Date createDate = resultSet.getDate(2);
                        java.sql.Date updateDate = resultSet.getDate(3);
                        Integer statusCode = resultSet.getInt(4);
                        String nameForSelect = resultSet.getString(5);
                        String password = resultSet.getString(6);
                        User user = new User();
                        user.setId(id);
                        user.setCreateTime(createDate);
                        user.setUpdateTime(updateDate);
                        user.setStatus(UserStatus.valueOfCode(statusCode));
                        user.setName(nameForSelect);
                        user.setPassword(password);
                        return user;
                    }
                }
            }catch (Exception e){
                log.error("failed to select user by name {}", name, e);
            }
        } catch (SQLException e) {
            log.error("failed to select user by name {}", name, e);
        }
        return null;
    }

    private Connection getConnectionFromDataSource() throws SQLException {
        return this.dataSource.getConnection();
    }

    private Connection getConnectionFromDriverManger() throws SQLException {
        try {
            Class.forName(this.driverClassName);
        } catch (ClassNotFoundException e) {
            log.error("failed to find driver {}", this.driverClassName, e);
        }
        return DriverManager.getConnection(url, userName, password);
    }

    private String formatDate(Date date){
        SimpleDateFormat dataFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return dataFormat.format(date);
    }

    private List<User> listUser(){
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc_demo";
        String userName = "root";
        String password = "root";
        List<User> users = new ArrayList<>();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            log.error("failed to find mysql driver", e);
        }
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, userName, password);
            Statement statement = connection.createStatement();
            String sql = "select id, name from tb_user";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                Long id = resultSet.getLong(1);
                String name = resultSet.getString(2);
                User user = new User();
                user.setId(id);
                user.setName(name);
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error("failed to close connection", e);
                }
            }
        }
        return null;
    }

    private List<User> listUser2(){
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc_demo";
        String userName = "root";
        String password = "root";
        List<User> users = new ArrayList<>();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            log.error("failed to find mysql driver", e);
        }
        try(Connection connection = DriverManager
                .getConnection(url, userName, password)){
            Statement statement = connection.createStatement();
            String sql = "select id, name from tb_user";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                Long id = resultSet.getLong(1);
                String name = resultSet.getString(2);
                User user = new User();
                user.setId(id);
                user.setName(name);
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            log.error("failed to execute", e);
        }
        return null;
    }

    private void update2(){
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc_demo";
        String userName = "root";
        String password = "root";
        List<User> users = new ArrayList<>();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            log.error("failed to find mysql driver", e);
        }
        try(Connection connection = DriverManager
                .getConnection(url, userName, password)){
            Statement statement = connection.createStatement();
            String sql = "update tb_user set name = '张三' where id = 1";
            int rows = statement.executeUpdate(sql);
        } catch (SQLException e) {
            log.error("failed to execute", e);
        }

    }

}
